﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

namespace Accountability4net.MySQLDbProvider
{
    public class MySQLDbAccount: BaseAccount
    {
        MySQLDbProvider provider_;
        protected MySQLDbAccount()
            : base()
        {
        }

        public MySQLDbAccount(MySQLDbProvider provider)
            : this()
        {
            provider_ = provider;
        }

        public void WirePropertyChanged()
        {
            PropertyChanged += MySQLAdoDbAccount_PropertyChanged;
        }

        void MySQLAdoDbAccount_PropertyChanged(object sender, System.ComponentModel.PropertyChangedEventArgs e)
        {
            string sql = "UPDATE `account`"
                + " SET `Balance` = @balance"
                + ", `Name` = @name"
                + ", `Description` = @description"
                + " WHERE `Number` = @number";
            using (var command = new MySqlCommand(sql, provider_.Connection))
            {
                MySqlParameter numberParameter = new MySqlParameter("@number", Number);
                command.Parameters.Add(numberParameter);
                MySqlParameter balanceParameter = new MySqlParameter("@balance", Balance);
                command.Parameters.Add(balanceParameter);
                MySqlParameter nameParameter = new MySqlParameter("@name", Name);
                command.Parameters.Add(nameParameter);
                MySqlParameter descriptionParameter = new MySqlParameter("@description", Description);
                command.Parameters.Add(descriptionParameter);
                int rows = command.ExecuteNonQuery();
            }
        }

        public static MySQLDbAccount Create(MySQLDbProvider provider, int number, AccountType type)
        {
            MySQLDbAccount account = new MySQLDbAccount(provider);
            account.Number = number;
            account.Type = type;
            string sql = "INSERT INTO `account` (`Number`, `Type`) VALUES (@number, @type)";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                MySqlParameter numberParameter = new MySqlParameter("@number", number);
                command.Parameters.Add(numberParameter);
                MySqlParameter typeParameter = new MySqlParameter("@type", type.ToString());
                command.Parameters.Add(typeParameter);
                int rows = command.ExecuteNonQuery();
            }
            account.WirePropertyChanged();
            return account;
        }


        public static IAccount Retrieve(MySQLDbProvider provider, int number)
        {
            MySQLDbAccount account = new MySQLDbAccount(provider);
            string sql = "SELECT * FROM `account` WHERE `Number` = @number";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                MySqlParameter numberParameter = new MySqlParameter("@number", number);
                command.Parameters.Add(numberParameter);
                using (var reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        account.Number = reader.GetInt32("Number");
                        account.Type = (AccountType)Enum.Parse(typeof(AccountType), reader.GetString("Type"));
                        account.Balance = reader.GetDecimal("Balance");
                        account.Name = provider.GetString(reader, "Name");
                        account.Description = provider.GetString(reader, "Description");
                        account.WirePropertyChanged();
                    }
                    else
                    {
                        return null;
                    }
                    reader.Close();
                }
            }
            return account;
        }

        public static IList<IAccount> Retrieve(MySQLDbProvider provider, int from, int limit)
        {
            var result = new List<IAccount>();
            string sql = "SELECT * FROM `account` WHERE `Number` >= @from LIMIT @limit";
            using (var command = new MySqlCommand(sql, provider.Connection))
            {
                MySqlParameter fromParameter = new MySqlParameter("@from", from);
                command.Parameters.Add(fromParameter);
                MySqlParameter limitParameter = new MySqlParameter("@limit", limit);
                command.Parameters.Add(limitParameter);
                using (var reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        MySQLDbAccount account = new MySQLDbAccount(provider);
                        account.Number = reader.GetInt32("Number");
                        account.Type = (AccountType)Enum.Parse(typeof(AccountType), reader.GetString("Type"));
                        account.Balance = reader.GetDecimal("Balance");
                        account.Name = provider.GetString(reader, "Name");
                        account.Description = provider.GetString(reader, "Description");
                        account.WirePropertyChanged();
                        result.Add(account);
                    }
                    reader.Close();
                }
            }
            return result;
        }
    }
}
